package com.zz.comframe.properties;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.security.Key;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import java.util.regex.Pattern;

import treemap.zz.json.JSONException;
import treemap.zz.json.JSONObject;

import com.zz.comframe.DBHelp;

public final class PropertiesPool {

	private static Key mik;
	private static Properties encryptpro;
	private static Properties commonpro;

	// private static void init() {
	static {
		try {
			String filepath = (new File(".")).getCanonicalPath()+"\\WebRoot\\WEB-INF\\classes\\com\\zz\\comframe\\properties";
			//System.out.println(PropertiesPool.class.getResource("/")); //Class文件所在路径

			// load key
			//mik = DESEncryptUtil.getKey(new FileInputStream(filepath+"\\mik"));
			mik = DESEncryptUtil.getKey(new FileInputStream(PropertiesPool.class.getResource("mik").getPath()));
			// load and decrypt encrypted properts
			//encryptpro = PropertiesPool.getEncryptProfile(filepath+"\\en_db.profile");
			encryptpro = PropertiesPool.getEncryptProfile(PropertiesPool.class.getResource("en_db.profile").getPath());
			// load common properts
			/*	String classname = "org.apache.derby.jdbc.ClientDriver";
				Class.forName(classname);
				Connection connection = DriverManager.getConnection(PropertiesPool.getEncryptpro("connstring-derby"), "db2012",
						"dusc2012");
				PreparedStatement pStatement = connection.prepareStatement("SELECT prokey, provalue FROM PROFILE_JQG");
				ResultSet resultSet = pStatement.executeQuery();
				Properties p = new Properties();
				while (resultSet.next()){
					String s1 = resultSet.getString(1);
					String s2 = resultSet.getString(2);
					p.put(s1, s2);
				}
				commonpro = p;
*/
			commonpro = PropertiesPool.getdbproperties("SELECT prokey, provalue FROM PROFILE_JQG", "prokey", "provalue");
			// commonpro = Constants.getProfile("./bin/fruit.properties");

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public final static Properties getEncryptpro() {
		return encryptpro;
	}

	public final static Properties getCommonpro() {
		return commonpro;
	}

	public final static String getEncryptpro(String prokey) {
		return PropertiesPool.getEncryptpro() != null ? PropertiesPool.getEncryptpro().getProperty(prokey) : null;
	}

	public final static String getCommonpro(String prokey) {
		return PropertiesPool.getCommonpro() != null ? PropertiesPool.getCommonpro().getProperty(prokey) : null;
	}

	public final static Key getMik() {
		return mik;
	}

	private static Properties getProfile(String profilename) throws FileNotFoundException, IOException {
		Properties pro = new Properties();
		File f = new File(profilename);
		if (f.exists()) {
			pro.load(new FileInputStream(f));
		} else
			pro = null;
		return pro;
	}

	private static Properties getEncryptProfile(String profilename) throws FileNotFoundException, IOException {
		Properties pro = new Properties();
		File f = new File(profilename);
		if (f.exists()) {
			pro.load(DESEncryptUtil.doDecrypt(PropertiesPool.getMik(), new FileInputStream(f)));
		} else
			pro = null;
		return pro;
	}

	private static Properties getdbproperties(String sql, String key, String value) {
		Properties result = new Properties();
		DBHelp db = new DBHelp();
		Connection connection = null;
		PreparedStatement pStatement = null;
		ResultSet resultSet = null;
		try {
			connection = db.getConnection();
			pStatement = connection.prepareStatement(sql);
			resultSet = pStatement.executeQuery();
			while (resultSet.next())
				result.put(resultSet.getString(key), resultSet.getString(value));
			result = result.size() > 0 ? result : null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeResource(connection, pStatement, resultSet);
		}
		return result;

	}
	
	/**
	 * 针对JQGrid的条件操作符执行转换，转为SQL表达式
	 * @param feilds    字段：
	 * @param opstr    JQGrid操作符：
	 * @param strobj    值：若为字符则已包含”‘“，如’student'，为数值时不含”‘“
	 * @return SQl表达式：
	 */
    public static final String getJQGridSQLExpression(String feilds, String op, String strobj) {
		String expression = "";
		feilds = feilds.trim();
		op = op.trim();
		strobj = strobj.trim();
		if (op != null && feilds != null) {

			// ['equal','not equal','less','less or equal','greater','greater or equal','begins with','does not begin with','is in','is not in','ends with','does not end with','contains','does not contain']
			String[] opsarr = { "cn", 	"nc",		"eq", 	"bw", 		"bn", 		"ne", 	"lt", 	"le", 	"gt", "ge", "ew", 		"en",  		"nu", 			"nn" ,			"in", 	"ni" };
			String[] ops = {" like ", " not like "," = ",	" like ", " not like ", " <> ", " < ", " <= ", " > ", ">=", " like ", " not like ", " is null ", " is not null ", " in ", " not in " };
			// 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
			for (int i = 0; i < ops.length; i++) {
				if (opsarr[i].equalsIgnoreCase(op)) {
					expression += " " + feilds + ops[i]; // "col1 = "
					if (strobj != null) {
						expression += "bw,bn".indexOf(opsarr[i]) >= 0 ? "'" + strobj.replaceAll("'", "") + "%' " : ""; // "col1 like 'xxx%'"
						expression += "ew,en".indexOf(opsarr[i]) >= 0 ? "'%" + strobj.replaceAll("'", "") + "' " : ""; // "col1 like '%xxx'"
						expression += "cn,nc".indexOf(opsarr[i]) >= 0 ? "'%" + strobj.replaceAll("'", "") + "%' " : ""; // "col1 like '%xxx%'"
						expression += "bw,bn,ew,en,cn,nc".indexOf(opsarr[i]) < 0 || strobj.indexOf("'") < 0 ? strobj : "";// "col1 = 'xxx'"
						// or "col1 = 4.67"
					}
					break;
				}
			}
		}
		if (expression.trim().equalsIgnoreCase(""))
			expression = null;
		
		return expression;
	}
    
    /**
     * 处理where子句
     * json-- "{"groupOp":"AND","rules":[{"field":"col1","op":"bw","data":"23"},{"field":"col2","op":"bw","data":"2"}]}"
     */
	public static final String getJQGridWhere(String js) {
		String where = "";
		try {
			JSONObject json = js != null ? new JSONObject(js) : null;
			if (json != null && json.getJSONArray("rules") != null && json.getJSONArray("rules").length() > 0) {
				String groupOp = json.getString("groupOp").trim().equalsIgnoreCase("and") ? " and " : " or ";
				for(int i=0; i<json.getJSONArray("rules").length(); i++){
					JSONObject jo = json.getJSONArray("rules").getJSONObject(i);
					String str = null;
					if(!hasSpChar(jo.getString("field")+jo.getString("op")+jo.getString("data")))	//sql valide
						str = getJQGridSQLExpression(jo.getString("field"), jo.getString("op"), jo.getString("data"));
					where += str!=null ? str : (groupOp.indexOf("and")>=0 ? " 1=1 ":"1=0 ");
					where += i<json.getJSONArray("rules").length()-1 ? groupOp : "";	
				}
				where = " where " + where;
			}
		} catch (JSONException e) {

			e.printStackTrace();
		} finally {
			// db.closeResource(connection, pStatement, resultSet);
		}
		return where;
	}
	/**
	 * 正则表达式检查是否含特殊字符（用于sql的where等子句参数检查
		//"[a-z]*[A-Z]*\\d*-*_*\\s*"    包含“-“和”-“和a-z，A-Z，0-9的表达式
	 */
	public static final boolean hasSpChar(String str){
		return str.replaceAll("[a-z]*[A-Z]*\\d*\\s*", "").length() <= 0;	//若无特殊字符，则被全部替换
	}
	

	/**过滤 ‘
	*ORACLE 注解 --  
	*关键字过滤 update ,delete
	**/ 
	private final static String reg = "(?:')|(?:--)|(/\\*(?:.|[\\n\\r])*?\\*/)|"
				+ "(\\b(select|update|and|or|delete|insert|trancate|char|into|substr|ascii|declare|exec|count|master|into|drop|execute)\\b)";
	private final static Pattern sqlPattern = Pattern.compile(reg, Pattern.CASE_INSENSITIVE);
	/***************************************************************************
	 * 参数校验
	 * @param str
	 */
	public static final boolean SQLInjectionValid(String str) {
		return !sqlPattern.matcher(str).find();		
			//logger.error("未能通过过滤器：p=" + p);			
	}
	
	/**
	 * 产生UUID，用于自动数据库生成ID
	 * @return	36bit String UUID
	 */
	public static final String getUUidstr(){
		return java.util.UUID.randomUUID().toString();
	}
	
    public static final String IsoToUtf(String str){
		try {	//将ISO-8859-1转为UTF-8，可能会有变化
			str	= new String(str.getBytes("ISO-8859-1"), "UTF-8");
		}
		catch (UnsupportedEncodingException ex) {
			str = null;
		}
		return str;
    }

//    public static boolean isNum(String str){
//		boolean flag = true;
//    	try{ 
//			new Integer(str); 
//		}catch(Exception e) 
//		{ 
//			flag=false;// 发生异常表示不是数字 
//		} 
//		return flag;
//    }
    
}
